﻿CREATE database DOAN_HK1
GO
USE DOAN_HK1
GO

CREATE TABLE KHACHHANG
(
	MA_KHACHHANG		INT identity,
	HO_VA_TEN			NVARCHAR(50) NOT NULL,
	SO_DIEN_THOAI		CHAR(12) NOT NULL,
	THU_DIEN_TU			NVARCHAR(25) NOT NULL,
	DIA_CHI				NVARCHAR(100) NOT NULL,
	PRIMARY KEY (MA_KHACHHANG)		
)
GO

CREATE TABLE  THANHVIEN
(
	MA_THANHVIEN		INT identity,
	HO_VA_TEN			NVARCHAR(50) ,
	SO_DIEN_THOAI		CHAR(12) NOT NULL,
	THU_DIEN_TU			NVARCHAR(25) NOT NULL,
	DIA_CHI				NVARCHAR(100) NOT NULL,
	GIOI_TINH			CHAR(10) NOT NULL,
	NAM_SINH			DATETIME NOT NULL,
	ID_USER				VARCHAR(50) NOT NULL,
	PASS_USER			VARCHAR(50) NOT NULL,
	NGAY_DANGKY			DATETIME DEFAULT GETUTCDATE() NOT NULL,
	DIEM_TICHLUY		INT,
	ACTIVES				INT,

	PRIMARY KEY (MA_THANHVIEN)
)
GO
CREATE TABLE SANPHAM
(
	MA_SANPHAM		INT identity,
	TEN_SANPHAM		NVARCHAR(50) NOT NULL,
	SOLUONG			INT NOT NULL,
	MA_DANHMUC		INT NOT NULL,
	MA_KHUYENMAI	INT,
	ACTIVE			INT NOT NULL,
	PRIMARY KEY	(MA_SANPHAM)
)
GO
CREATE TABLE SANPHAM_CT
(
	MA_SANPHAM_CT	INT IDENTITY,
	MA_SANPHAM		INT NOT NULL,
	DON_GIA			INT NOT NULL,
	URL_HINH_1		NCHAR(200) NOT NULL,
	URL_HINH_2		NCHAR(200) NOT NULL,
	URL_HINH_3		NCHAR(200) NOT NULL,
	URL_VIDEO		NCHAR(200) NOT NULL,
	MOTA			NCHAR(2000) NOT NULL,
	PRIMARY KEY (MA_SANPHAM_CT)
)
GO
CREATE TABLE HOADON
(
	MA_HOADON			INT identity,
	NGAY_LAP_HOADON		DATETIME GETUTCDATE() NOT NULL,
	MA_KHACHHANG		INT NOT NULL,
	TRANG_THAI			INT NOT NULL,	
	MA_THANHVIEN		INT NOT NULL,	
	PRIMARY	KEY(MA_HOADON)
)	
GO
CREATE TABLE HOADON_CHITIET
(
	MA_HOADONCT			INT identity,
	MA_SANPHAM			INT NOT NULL,
	SOLUONG				INT NOT NULL,
	TONG_TIEN			INT NOT NULL,				
	PRIMARY KEY(MA_HOADONCT)
)
GO
CREATE TABLE KHUYENMAI
(
	MA_KHUYENMAI		INT identity,
	HINH_THUC_KM		INT NOT NULL,
	ACTIVE				INT,
	DAYBEGIN			DATETIME,
	DAYEND				DATETIME,		
	PRIMARY KEY (MA_KHUYENMAI)
)
GO
CREATE TABLE DANHMUC
(
	MA_DANHMUC		INT identity,
	TEN_DANHMUC		NVARCHAR(50) NOT NULL,
	ACTIVE			INT NOT NULL,
	PRIMARY KEY (MA_DANHMUC)	
)
GO
CREATE TABLE COUPONS_CT
(
	MA_COUPONS_CT	INT identity,
	MA_COUPONS		INT NOT NULL,
	TEN				NVARCHAR(30) NOT NULL,
	HINHTHUC		INT NOT NULL,
	MOTA			NVARCHAR(50) NOT NULL,
	HINHANH			VARCHAR(100) NOT NULL,
	ACTIVE			INT NOT NULL,
	PRIMARY KEY (MA_COUPONS_CT)
)
GO
CREATE TABLE COUPONS
(
	MA_COUPONS		INT identity,
	MA_THANHVIEN	INT ,
	MA_COUPONS_CT	INT NOT NULL,
	PRIMARY KEY (MA_COUPONS)
)
GO
CREATE TABLE QL_ADMIN
(
	MA_ADMIN		INT identity,
	TEN_ADMIN		VARCHAR(20) NOT NULL,
	PASS			VARCHAR(20) NOT NULL,
	MA_QUYEN		INT NOT NULL,
	ACTIVE			INT NOT NULL,
	PRIMARY KEY (MA_ADMIN)

)
GO
CREATE TABLE CT_QUYEN
(
	MA_QUYEN		INT identity,
	MOTA			NVARCHAR(100) NOT NULL,
	PRIMARY KEY (MA_QUYEN)
)
GO


ALTER TABLE HOADON_CHITIET
ADD CONSTRAINT FK_HOADON_CHITIET_HOADON FOREIGN KEY (MA_HOADONCT) REFERENCES HOADON(MA_HOADON)
ALTER TABLE HOADON_CHITIET
ADD CONSTRAINT FK_HOADON_CHITIET_SANPHAM FOREIGN KEY (MA_SANPHAM) REFERENCES SANPHAM(MA_SANPHAM)
ALTER TABLE HOADON
ADD CONSTRAINT FK_HOADON_KHACHHANG FOREIGN KEY (MA_KHACHHANG) REFERENCES KHACHHANG(MA_KHACHHANG)
ALTER TABLE HOADON
ADD CONSTRAINT FK_HOADON_THANHVIEN FOREIGN KEY (MA_THANHVIEN) REFERENCES THANHVIEN(MA_THANHVIEN)
ALTER TABLE SANPHAM
ADD CONSTRAINT FK_SANPHAM_KHUYENMAI	 FOREIGN KEY  (MA_KHUYENMAI) REFERENCES KHUYENMAI(MA_KHUYENMAI)
ALTER TABLE SANPHAM
ADD CONSTRAINT FK_SANPHAM_DANHMUC FOREIGN KEY (MA_DANHMUC) REFERENCES DANHMUC(MA_DANHMUC)
ALTER TABLE COUPONS
ADD CONSTRAINT FK_COUPONS_THANHVIEN FOREIGN KEY (MA_THANHVIEN) REFERENCES THANHVIEN(MA_THANHVIEN)
ALTER TABLE COUPONS
ADD CONSTRAINT FK_COUPONS_COUPONS_CT FOREIGN KEY (MA_COUPONS_CT) REFERENCES COUPONS_CT(MA_COUPONS_CT)
ALTER TABLE SANPHAM_CT
ADD CONSTRAINT FK_SANPHAM_CT_SANPHAM FOREIGN KEY (MA_SANPHAM) REFERENCES SANPHAM(MA_SANPHAM)
/*ALTER TABLE CT_ADMIN
ADD CONSTRAINT FK_CT_ADMIN_QL_ADMIN FOREIGN KEY (MA_ADMIN) REFERENCES QL_ADMIN(MA_ADMIN)
/*ALTER TABLE SANPHAM_KHUYENMAI*/
ADD CONSTRAINT FK_SANPHAM_KHUYENMAI_KHUYENMAI FOREIGN KEY (MA_KHUYENMAI) REFERENCES SANPHAM_KHUYENMAI(MA_KHUYENMAI)
   */
GO

-- STORT INSERT

CREATE PROC INS_THANHVIEN_PROC
(@HO_VA_TEN NVARCHAR(50),@SO_DIEN_THOAI CHAR(12),@THU_DIEN_TU NVARCHAR(25),@DIA_CHI NVARCHAR(100),@GIOI_TINH CHAR(10),@NAM_SINH DATETIME,@ID_USER VARCHAR(50),@PASS_USER VARCHAR(50),@ACTIVE INT)
AS
BEGIN
INSERT INTO THANHVIEN(HO_VA_TEN,SO_DIEN_THOAI,THU_DIEN_TU,DIA_CHI,GIOI_TINH,NAM_SINH,ID_USER,PASS_USER,ACTIVES)
VALUES (@HO_VA_TEN,@SO_DIEN_THOAI,@THU_DIEN_TU,@DIA_CHI,@GIOI_TINH,@NAM_SINH,@ID_USER,@PASS_USER,@ACTIVE)

END
GO

EXEC INS_THANHVIEN_PROC N'LÊ LÊ','0987536658','LELE2407@HOTMAIL.COM',N'204/12A QUỐC LỘ 13','NAM','7/24/1987','MRLE247','3650352',1
GO

CREATE PROC INS_SANPHAM_PROC
(@TEN)
BEGIN

END
GO

SELECT * FROM THANHVIEN
GO

